﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.Toook.AutoDownloadHtml.Entity;
using System.Data.SQLite;
using System.Data;
using Com.Toook.AutoDownloadHtml.Util;

namespace Com.Toook.AutoDownloadHtml.SaveData
{
    public class NewsSqlite
    {
        public static void TestSave()
        {
            News news = new News();
            news.Id = 1;
            news.Title = "title";
            news.Content = "content";
            news.PubDate = "2013-02-01";
            news.SourceUrl = "url";
            news.TypeSite = "worldToook";
            string insert = @"insert into News(Id, Title, PubDate, SourceUrl, TypeSite, Content)
                    values(@Id, @Title, @PubDate, @SourceUrl, @TypeSite, @Content)";
            using(SQLiteConnection con = SqliteHelper.GetConnection())
            {
                SQLiteCommand cmd = new SQLiteCommand(insert, con);
                
                cmd.Parameters.Add("@Id", DbType.Int32).Value = news.Id;
                cmd.Parameters.Add("@Title", DbType.String).Value = news.Title;
                cmd.Parameters.Add("@PubDate", DbType.String).Value = news.PubDate;
                cmd.Parameters.Add("@SourceUrl", DbType.String).Value = news.SourceUrl;
                cmd.Parameters.Add("@TypeSite", DbType.String).Value = news.TypeSite;
                cmd.Parameters.Add("@Content", DbType.String).Value = news.Content;
                con.Open();
                cmd.ExecuteNonQuery();               
            }
        }
        /// <summary>
        /// 保存新闻对象 news 到数据库
        /// </summary>
        /// <param name="news">新闻对象</param>
        /// <returns>如果存在相同url,返回false</returns>
        public static bool SaveNews(News news)
        {
            if(HaveSameNews(news.SourceUrl))
            {
                return false;
            }
            string insert = @"insert into News(Id, Title, PubDate, SourceUrl, TypeSite, Content)
                    values(@Id, @Title, @PubDate, @SourceUrl, @TypeSite, @Content)";
            SQLiteConnection con = null;
            try
            {
                con = SqliteHelper.GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(insert, con);
                cmd.Parameters.Add("@Id", DbType.Int32).Value = news.Id;
                cmd.Parameters.Add("@Title", DbType.String).Value = news.Title;
                cmd.Parameters.Add("@PubDate", DbType.String).Value = news.PubDate;
                cmd.Parameters.Add("@SourceUrl", DbType.String).Value = news.SourceUrl;
                cmd.Parameters.Add("@TypeSite", DbType.String).Value = news.TypeSite;
                cmd.Parameters.Add("@Content", DbType.String).Value = news.Content;

                con.Open();
                cmd.ExecuteNonQuery();               
            }
            catch(SQLiteException se)
            {
                LogHelper.Log.Error("SaveNews error", se);
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, null);
            }
            return true;
        }
        /// <summary>
        /// 查询是否存在相同URL的新闻
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        private static bool HaveSameNews(string url)
        {
            bool hasSameNews = false;           
            string sql = "SELECT COUNT(*) AS N FROM News WHERE SourceUrl=@SourceUrl";
            SQLiteConnection con = null;
            SQLiteDataReader reader = null;
            try
            {
                con = SqliteHelper.GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);
                cmd.Parameters.Add("@SourceUrl", DbType.String).Value = url;

                con.Open();
                reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    hasSameNews = reader.GetInt32(0) > 0;
                }
            }
            catch(SQLiteException se)
            {
                LogHelper.Log.Error("GetNewsUpdateObj error.", se);
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, reader);
            }
            return hasSameNews;
        }
        /// <summary>
        /// 获取更新新闻分类参数
        /// </summary>
        /// <returns></returns>
        public static NewsUpdateObj GetNewsUpdateObj()
        {
            NewsUpdateObj obj = new NewsUpdateObj();
            string sql = "SELECT Id, TypeSite, Url, ExpiryTime, IntervalHour, Charset FROM NewsTypeSite ORDER BY ExpiryTime ASC LIMIT 1 ";
            SQLiteConnection con = null;
            SQLiteDataReader reader = null;
            try
            {
                con = SqliteHelper.GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);
                con.Open();
                reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    obj.Id = reader.GetInt32(0);
                    obj.TypeSite = reader.GetString(1);
                    obj.Url = reader.GetString(2);
                    obj.ExpiryTime = reader.GetDateTime(3);
                    obj.IntervalHour = reader.GetInt32(4);
                    object charset = reader[5];
                    if(charset != DBNull.Value && charset != null )
                    { 
                        obj.Charset = charset.ToString();
                    }
                }
            }
            catch(SQLiteException se)
            {
                LogHelper.Log.Error("GetNewsUpdateObj error.", se);
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, reader);
            }
            return obj;
        }
        /// <summary>
        /// 更新新闻分类参数
        /// </summary>
        /// <returns></returns>
        public static void UpdateNewsUpdateObj(NewsUpdateObj obj)
        {
            string sql = "UPDATE NewsTypeSite SET ExpiryTime=@ExpiryTime WHERE Id=@Id ";
            SQLiteConnection con = null;
            try
            {
                con = SqliteHelper.GetConnection();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);
                cmd.Parameters.Add("@ExpiryTime", DbType.DateTime).Value = DateTime.Now.AddHours(obj.IntervalHour);
                cmd.Parameters.Add("@Id", DbType.Int32).Value = obj.Id;

                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch(SQLiteException se)
            {
                LogHelper.Log.Error("UpdateNewsUpdateObj error.", se);
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, null);
            }
        }
    }
}
